﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ES_Project.Model;

namespace ES_Project.Pages
{
    public partial class WebForm665 : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand();

        #region "Page_Load"
        protected void Page_Load(object sender, EventArgs e)

        {
            if (!IsPostBack) {
                RadioButtonList5.SelectedValue = "1";
                RadioButtonList3.SelectedValue = "0";
                RadioButtonList6.SelectedValue = "0";
                RadioButtonList7.SelectedValue = "0";
                CMD_EDIT.Enabled = false;
                CMD_EDIT_student.Enabled = false;
                CMD_EDIT_customer.Enabled = false;
                //BindData();
            }
            
            
        }
        #endregion

        


        #region "Search for empCOde"
        protected void CMD_EmpCode_Click(object sender, EventArgs e)
        {


            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            strSQL = "SELECT * FROM  emp_mast em  " +
                     "where em.emp_type = '0' and em.emp_code = '" + empCode.Text + "  '";
            //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
            //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
            //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
            //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            //dtAdapter.Fill(dt);
            da4 = null;
            Conn.Close();
            Conn = null;
            //dtAdapter = null;
            //objConn.Close();
            //objConn = null;


            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {


                empName.Text = (string)ds4.Tables[0].Rows[0]["emp_name"].ToString();
                empId.Text = (string)ds4.Tables[0].Rows[0]["emp_id"].ToString();


            }
            //BindData();
            //BindDataleave();
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));
        }
        #endregion

        #region "Search for StCOde"
        protected void CMD_StCode_Click(object sender, EventArgs e)
        {


            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            strSQL = "SELECT * FROM  student_mast sm  " +
                     "where sm.st_code = '" + stCode.Text + "  '";
            //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
            //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
            //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
            //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            //dtAdapter.Fill(dt);
            da4 = null;
            Conn.Close();
            Conn = null;
            //dtAdapter = null;
            //objConn.Close();
            //objConn = null;


            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {


                stName.Text = (string)ds4.Tables[0].Rows[0]["st_name"].ToString();
                stId.Text = (string)ds4.Tables[0].Rows[0]["st_id"].ToString();


            }
            //BindData();
            //BindDataleave();
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));
        }
        #endregion

        #region "Search for CusCOde"
        protected void CMD_CusCode_Click(object sender, EventArgs e)
        {


            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            strSQL = "SELECT * FROM  emp_mast em  " +
                     "where em.emp_type = '1' and em.emp_code = '" + cusCode.Text + "  '";
            //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
            //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
            //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
            //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            //dtAdapter.Fill(dt);
            da4 = null;
            Conn.Close();
            Conn = null;
            //dtAdapter = null;
            //objConn.Close();
            //objConn = null;


            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {


                cusName.Text = (string)ds4.Tables[0].Rows[0]["emp_name"].ToString();
                cusId.Text = (string)ds4.Tables[0].Rows[0]["emp_id"].ToString();


            }
            //BindData();
            //BindDataleave();
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));
        }
        #endregion

        #region "GridView2_SelectedIndexChanging for teacher"
        protected void GridView2_SelectedIndexChanging(object sender, GridViewSelectEventArgs e) 
        {
            CMD_EDIT.Enabled = true;
            CMD_INSERT.Enabled = false;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            //strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, " +
            //         "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, " +
            //         "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name , ecm.pay_type, ecm.emp_cls_sal,ecm.emp_cls_sal_month,ecm.emp_cls_sal_day,ecm.emp_cls_sal_hours " +
            //         "FROM emp_cls_mast ecm " +
            //         "LEFT OUTER JOIN emp_mast em " +
            //            "ON ecm.emp_id = em.emp_id " +
            //         "LEFT OUTER JOIN cls_register_detail crd " +
            //            "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
            //         "LEFT OUTER JOIN cls_register_mast crm " +
            //            "ON crd.cls_reg_id = crm.cls_reg_id " +
            //         "where ecm.emp_cls_mast_id = '" + GridView1.DataKeys[e.NewSelectedIndex].Value + "'";

            strSQL = " SELECT * FROM cls_leave_detail cld left outer join emp_mast em on em.emp_id = cld.emp_id "+
                     " where cld.cls_leave_detail_id ='" + GridView2.DataKeys[e.NewSelectedIndex].Value + "' ";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                clsLeaveDetailId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_leave_detail_id"]).ToString();
                empId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["emp_id"]).ToString();
                empCode.Text = (string)ds4.Tables[0].Rows[i]["emp_code"].ToString();
                empName.Text = (string)ds4.Tables[0].Rows[i]["emp_name"].ToString();
                leaveDate.Text =  string.Format("{0:dd/MM/yyyy}", ds4.Tables[0].Rows[i]["leave_date"]).ToString();
                RadioButtonList5.SelectedValue = (string)ds4.Tables[0].Rows[i]["cus_type"].ToString();
                RadioButtonList3.SelectedValue = (string)ds4.Tables[0].Rows[i]["leave_type"].ToString();


                //empClsTotalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_total_hours"].ToString();
                //clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                //clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                //clsRegDetailId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                //empClsMastId.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_mast_id"].ToString();
                //clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                //empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                //empClsSal.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal"].ToString();
                //empClsSalMonth.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString()));
                //empClsSalMonth.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString();
                //empClsSalDay.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString()));
                //empClsSalDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString();
                //empClsSalHours.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString()));
                //empClsSalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString(); 
                //radio.SelectedValue = (string)ds4.Tables[0].Rows[i]["pay_type"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();
                //this.radio.DataSource = ds4;
                //this.radio.DataTextField = "SexDesc";
                //this.radio.DataValueField = "pay_type";
                //this.radio.DataBind();

                //string strSQL2;
                //strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                //     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                //     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                //MySqlConnection Conn1 = new MySqlConnection(StrConn);
                //Conn1.Open();
                //MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                //DataSet ds2 = new DataSet();

                //da2.Fill(ds2);
                ////*** DropDownList ***//
                //this.myDDL1.DataSource = ds2;
                //this.myDDL1.DataTextField = "cls_course_no";
                //this.myDDL1.DataValueField = "cls_reg_detail_id";
                //this.myDDL1.DataBind();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                //da2 = null;
                //Conn1.Close();
                //Conn1 = null;
                
            }
            
            
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));
        
        }
        #endregion

        #region "GridView4_SelectedIndexChanging for student"
        protected void GridView4_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {
            CMD_INSERT_student.Enabled = false;
            CMD_EDIT_student.Enabled = true;
           
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            //strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, " +
            //         "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, " +
            //         "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name , ecm.pay_type, ecm.emp_cls_sal,ecm.emp_cls_sal_month,ecm.emp_cls_sal_day,ecm.emp_cls_sal_hours " +
            //         "FROM emp_cls_mast ecm " +
            //         "LEFT OUTER JOIN emp_mast em " +
            //            "ON ecm.emp_id = em.emp_id " +
            //         "LEFT OUTER JOIN cls_register_detail crd " +
            //            "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
            //         "LEFT OUTER JOIN cls_register_mast crm " +
            //            "ON crd.cls_reg_id = crm.cls_reg_id " +
            //         "where ecm.emp_cls_mast_id = '" + GridView1.DataKeys[e.NewSelectedIndex].Value + "'";
            strSQL = " SELECT * FROM cls_leave_detail cld left outer join student_mast sm on sm.st_id = cld.st_id " +
                     " where cld.cls_leave_detail_id ='" + GridView4.DataKeys[e.NewSelectedIndex].Value + "' ";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                clsLeaveDetailId2.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_leave_detail_id"]).ToString();
                stId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["st_id"]).ToString();
                stCode.Text = (string)ds4.Tables[0].Rows[i]["st_code"].ToString();
                stName.Text = (string)ds4.Tables[0].Rows[i]["st_name"].ToString();
                leaveDate.Text = string.Format("{0:dd/MM/yyyy}", ds4.Tables[0].Rows[i]["leave_date"]).ToString();
                RadioButtonList5.SelectedValue = (string)ds4.Tables[0].Rows[i]["cus_type"].ToString();
                RadioButtonList6.SelectedValue = (string)ds4.Tables[0].Rows[i]["leave_type"].ToString();
                //clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                //empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                //empClsSal.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal"].ToString();
                //empClsSalMonth.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString()));
                //empClsSalMonth.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString();
                //empClsSalDay.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString()));
                //empClsSalDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString();
                //empClsSalHours.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString()));
                //empClsSalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString(); 
                //radio.SelectedValue = (string)ds4.Tables[0].Rows[i]["pay_type"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();
                //this.radio.DataSource = ds4;
                //this.radio.DataTextField = "SexDesc";
                //this.radio.DataValueField = "pay_type";
                //this.radio.DataBind();

                //string strSQL2;
                //strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                //     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                //     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                //MySqlConnection Conn1 = new MySqlConnection(StrConn);
                //Conn1.Open();
                //MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                //DataSet ds2 = new DataSet();

                //da2.Fill(ds2);
                ////*** DropDownList ***//
                //this.myDDL1.DataSource = ds2;
                //this.myDDL1.DataTextField = "cls_course_no";
                //this.myDDL1.DataValueField = "cls_reg_detail_id";
                //this.myDDL1.DataBind();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                //da2 = null;
                //Conn1.Close();
                //Conn1 = null;

            }
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));

        }
        #endregion

        #region "GridView5_SelectedIndexChanging for customer"
        protected void GridView5_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {
            CMD_INSERT_customer.Enabled = false;
            CMD_EDIT_customer.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            //strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, " +
            //         "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, " +
            //         "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name , ecm.pay_type, ecm.emp_cls_sal,ecm.emp_cls_sal_month,ecm.emp_cls_sal_day,ecm.emp_cls_sal_hours " +
            //         "FROM emp_cls_mast ecm " +
            //         "LEFT OUTER JOIN emp_mast em " +
            //            "ON ecm.emp_id = em.emp_id " +
            //         "LEFT OUTER JOIN cls_register_detail crd " +
            //            "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
            //         "LEFT OUTER JOIN cls_register_mast crm " +
            //            "ON crd.cls_reg_id = crm.cls_reg_id " +
            //         "where ecm.emp_cls_mast_id = '" + GridView1.DataKeys[e.NewSelectedIndex].Value + "'";

            strSQL = " SELECT * FROM cls_leave_detail cld left outer join emp_mast em on em.emp_id = cld.emp_id " +
                     " where cld.cls_leave_detail_id ='" + GridView5.DataKeys[e.NewSelectedIndex].Value + "' ";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                clsLeaveDetailId3.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_leave_detail_id"]).ToString();
                cusId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["emp_id"]).ToString();
                cusCode.Text = (string)ds4.Tables[0].Rows[i]["emp_code"].ToString();
                cusName.Text = (string)ds4.Tables[0].Rows[i]["emp_name"].ToString();
                leaveDate.Text = string.Format("{0:dd/MM/yyyy}", ds4.Tables[0].Rows[i]["leave_date"]).ToString();
                RadioButtonList5.SelectedValue = (string)ds4.Tables[0].Rows[i]["cus_type"].ToString();
                RadioButtonList7.SelectedValue = (string)ds4.Tables[0].Rows[i]["leave_type"].ToString();
                //empClsTotalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_total_hours"].ToString();
                //clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                //clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                //clsRegDetailId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                //empClsMastId.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_mast_id"].ToString();
                //clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                //empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                //empClsSal.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal"].ToString();
                //empClsSalMonth.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString()));
                //empClsSalMonth.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString();
                //empClsSalDay.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString()));
                //empClsSalDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString();
                //empClsSalHours.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString()));
                //empClsSalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString(); 
                //radio.SelectedValue = (string)ds4.Tables[0].Rows[i]["pay_type"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();
                //this.radio.DataSource = ds4;
                //this.radio.DataTextField = "SexDesc";
                //this.radio.DataValueField = "pay_type";
                //this.radio.DataBind();

                //string strSQL2;
                //strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                //     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                //     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                //MySqlConnection Conn1 = new MySqlConnection(StrConn);
                //Conn1.Open();
                //MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                //DataSet ds2 = new DataSet();

                //da2.Fill(ds2);
                ////*** DropDownList ***//
                //this.myDDL1.DataSource = ds2;
                //this.myDDL1.DataTextField = "cls_course_no";
                //this.myDDL1.DataValueField = "cls_reg_detail_id";
                //this.myDDL1.DataBind();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                //da2 = null;
                //Conn1.Close();
                //Conn1 = null;

            }
            //RefreshOpener(Page, Convert.ToInt32(ViewState["num"]));

        }
        #endregion

        #region "CMD_INSERT_Click for teacher"
        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT":

                        cls_leave_detail cld = new cls_leave_detail();
                        cld.leave_date = Convert.ToDateTime(leaveDate.Text);
                        //cld.leave_hours = leaveHours.Text;
                        cld.leave_type = RadioButtonList3.SelectedValue;
                        cld.cus_type = RadioButtonList5.SelectedValue;
                        //cld.leave_cls_day = leaveClsDay.Text;
                        //cld.leave_cls_end = leaveClsEnd.Text;
                        //cld.leave_cls_start = leaveClsStart.Text;
                        //cld.emp_cls_mast_id = Convert.ToInt32(empClsMastId.Text);
                        if (!empId.Text.Equals(""))
                        {
                            cld.emp_id = Convert.ToInt32(empId.Text);
                        }
                        cld.create_by = "Tong";
                        cld.create_date = DateTime.Now;
                        

                        
                        //pcm.emp_id = Convert.ToInt32(Request.QueryString["emp_id"]);
                        context.cls_leave_detail.AddObject(cld);
                        try
                        {
                            if (context.SaveChanges() > 0)
                            {

                                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('เพิ่มข้อมูลเรียบร้อยแล้ว')", true);
                                //GridView4.DataBind();
                                //BindData();
                                BindDataleave_teacher();
                                //GridView11.SelectedIndex = -1;
                                GridView2.SelectedIndex = -1;
                                CMD_INSERT.Enabled = false;
                                //CMD_EDIT.Enabled = false;
                                //clsRegCode.Text = "";
                                //clsName.Text = "";
                                //payId.Text = "";
                                //clsCourseTime.Text = "";
                                //clsCourseAmt.Text = "";
                                //clsCourseNo.Text = "";

                            }
                        }
                        catch (Exception ex)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert6", "alert('การชำระเงินซ้ำ')", true);
                        }
                        break;
                    case "CMD_EDIT":

                        List<cls_leave_detail> crm1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId.Text) + " ").ToList();
                        if (crm1.Count() == 1)
                        {
                            cls_leave_detail crmEdit1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId.Text) + " ").First();
                            if (crmEdit1 != null)
                            {
                                crmEdit1.update_by = "Tong";
                                crmEdit1.update_date = DateTime.Now;
                                crmEdit1.leave_date = Convert.ToDateTime(leaveDate.Text);
                                //crmEdit1.leave_hours = leaveHours.Text;
                                crmEdit1.leave_type = RadioButtonList3.SelectedValue;
                                crmEdit1.cus_type = RadioButtonList5.SelectedValue;
                                
                                if (!empId.Text.Equals(""))
                                {
                                    crmEdit1.emp_id = Convert.ToInt32(empId.Text);
                                }


                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert114", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    //GridView4.DataBind();
                                    BindDataleave_teacher();
                                    GridView2.SelectedIndex = -1;
                                    //CMD_INSERT.Enabled = true;
                                    CMD_EDIT.Enabled = false;
                                    //clsRegCode.Text = "";
                                    //clsName.Text = "";
                                    //pay_id.Text = "";
                                    empCode.Text = "";
                                    empName.Text = "";
                                    leaveDate.Text = "";

                                }
                            }
                        }
                        break;
                }
            }
        }

        
        #endregion


        #region "CMD_INSERT_Click for student"
        protected void CMD_INSERT_Click_student(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT_student":

                        cls_leave_detail cld = new cls_leave_detail();
                        cld.leave_date = Convert.ToDateTime(leaveDate.Text);
                        //cld.leave_hours = leaveHours.Text;
                        cld.leave_type = RadioButtonList6.SelectedValue;
                        cld.cus_type = RadioButtonList5.SelectedValue;
                        //cld.leave_cls_day = leaveClsDay.Text;
                        //cld.leave_cls_end = leaveClsEnd.Text;
                        //cld.leave_cls_start = leaveClsStart.Text;
                        //cld.emp_cls_mast_id = Convert.ToInt32(empClsMastId.Text);
                        if (!stId.Text.Equals(""))
                        {
                            cld.st_id = Convert.ToInt32(stId.Text);
                        }
                        cld.create_by = "Tong";
                        cld.create_date = DateTime.Now;



                        //pcm.emp_id = Convert.ToInt32(Request.QueryString["emp_id"]);
                        context.cls_leave_detail.AddObject(cld);
                        try
                        {
                            if (context.SaveChanges() > 0)
                            {

                                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('เพิ่มข้อมูลเรียบร้อยแล้ว')", true);
                                //GridView4.DataBind();
                                //BindData();
                                BindDataleave_student();
                                //GridView11.SelectedIndex = -1;
                                GridView4.SelectedIndex = -1;
                                CMD_INSERT_student.Enabled = false;
                                //CMD_EDIT_student.Enabled = false;
                                //clsRegCode.Text = "";
                                //clsName.Text = "";
                                //payId.Text = "";
                                //clsCourseTime.Text = "";
                                //clsCourseAmt.Text = "";
                                //clsCourseNo.Text = "";

                            }
                        }
                        catch (Exception ex)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert6", "alert('การชำระเงินซ้ำ')", true);
                        }
                        break;
                    case "CMD_EDIT_student":

                        List<cls_leave_detail> crm1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId2.Text) + " ").ToList();
                        if (crm1.Count() == 1)
                        {
                            cls_leave_detail crmEdit1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId2.Text) + " ").First();
                            if (crmEdit1 != null)
                            {
                                crmEdit1.update_by = "Tong";
                                crmEdit1.update_date = DateTime.Now;
                                crmEdit1.leave_date = Convert.ToDateTime(leaveDate.Text);
                                //crmEdit1.leave_hours = leaveHours.Text;
                                crmEdit1.leave_type = RadioButtonList6.SelectedValue;
                                crmEdit1.cus_type = RadioButtonList5.SelectedValue;

                                if (!stId.Text.Equals(""))
                                {
                                    crmEdit1.st_id = Convert.ToInt32(stId.Text);
                                }



                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    //GridView4.DataBind();
                                    //BindData();
                                    BindDataleave_student();
                                    GridView4.SelectedIndex = -1;
                                    //CMD_INSERT.Enabled = true;
                                    CMD_EDIT_student.Enabled = false;
                                    //clsRegCode.Text = "";
                                    //clsName.Text = "";
                                    //pay_id.Text = "";
                                    //clsCourseTime.Text = "";
                                    //clsCourseAmt.Text = "";
                                    //clsCourseNo.Text = "";

                                }
                            }
                        }
                        break;
                }
            }
        }


        #endregion

        #region "CMD_INSERT_Click for customer"
        protected void CMD_INSERT_Click_customer(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT_customer":

                        cls_leave_detail cld = new cls_leave_detail();
                        cld.leave_date = Convert.ToDateTime(leaveDate.Text);
                        //cld.leave_hours = leaveHours.Text;
                        cld.leave_type = RadioButtonList7.SelectedValue;
                        cld.cus_type = RadioButtonList5.SelectedValue;
                        //cld.leave_cls_day = leaveClsDay.Text;
                        //cld.leave_cls_end = leaveClsEnd.Text;
                        //cld.leave_cls_start = leaveClsStart.Text;
                        //cld.emp_cls_mast_id = Convert.ToInt32(empClsMastId.Text);
                        if (!cusId.Text.Equals(""))
                        {
                            cld.emp_id = Convert.ToInt32(cusId.Text);
                        }
                        cld.create_by = "Tong";
                        cld.create_date = DateTime.Now;



                        //pcm.emp_id = Convert.ToInt32(Request.QueryString["emp_id"]);
                        context.cls_leave_detail.AddObject(cld);
                        try
                        {
                            if (context.SaveChanges() > 0)
                            {

                                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert117", "alert('เพิ่มข้อมูลเรียบร้อยแล้ว')", true);
                                //GridView4.DataBind();
                                //BindData();
                                BindDataleave_customer();
                                //GridView11.SelectedIndex = -1;
                                GridView5.SelectedIndex = -1;
                                CMD_INSERT_customer.Enabled = false;
                                //CMD_EDIT.Enabled = false;
                                //clsRegCode.Text = "";
                                //clsName.Text = "";
                                //payId.Text = "";
                                //clsCourseTime.Text = "";
                                //clsCourseAmt.Text = "";
                                //clsCourseNo.Text = "";

                            }
                        }
                        catch (Exception ex)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert6", "alert('การชำระเงินซ้ำ')", true);
                        }
                        break;
                    case "CMD_EDIT_customer":

                        List<cls_leave_detail> crm1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId3.Text) + " ").ToList();
                        if (crm1.Count() == 1)
                        {
                            cls_leave_detail crmEdit1 = context.cls_leave_detail.Where("it.[cls_leave_detail_id]=" + Convert.ToInt32(clsLeaveDetailId3.Text) + " ").First();
                            if (crmEdit1 != null)
                            {
                                crmEdit1.update_by = "Tong";
                                crmEdit1.update_date = DateTime.Now;
                                crmEdit1.leave_date = Convert.ToDateTime(leaveDate.Text);
                                //crmEdit1.leave_hours = leaveHours.Text;
                                crmEdit1.leave_type = RadioButtonList7.SelectedValue;
                                crmEdit1.cus_type = RadioButtonList5.SelectedValue;

                                if (!cusId.Text.Equals(""))
                                {
                                    crmEdit1.emp_id = Convert.ToInt32(cusId.Text);
                                }


                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert118", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    //GridView4.DataBind();
                                    BindDataleave_customer();
                                    GridView5.SelectedIndex = -1;
                                    //CMD_INSERT.Enabled = true;
                                    CMD_EDIT_customer.Enabled = false;
                                    //clsRegCode.Text = "";
                                    //clsName.Text = "";
                                    //pay_id.Text = "";
                                    cusCode.Text = "";
                                    cusName.Text = "";
                                    leaveDate.Text = "";

                                }
                            }
                        }
                        break;
                }
            }
        }


        #endregion

        #region "BindDataleave_teacher()"
        void BindDataleave_teacher()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            

            strSQL = "SELECT cld.emp_id, "+
                     "em.emp_name, "+
                     "em.emp_code, "+
                     "cld.leave_date, "+
                     "cld.leave_hours, "+
                     "cld.leave_type, "+
                     "cld.cls_leave_detail_id, "+
                     "cld.cus_type, "+
                     "IF(cld.leave_type = '0', 'ลาล่วงหน้า', 'ลาทีหลัง') AS leave_type_desc, " +
                     "IF(cld.cus_type = '0', 'ครู', IF(cld.cus_type = '1', 'นักเรียน','พนักงาน')) AS cus_type_desc " +
                     "FROM    cls_leave_detail cld "+
                     "LEFT OUTER JOIN "+
                     "emp_mast em "+
                     "ON cld.emp_id = em.emp_id "+
                     " where cld.emp_id = '"+empId.Text+"' "+
                     " and cld.leave_date = '"+toDate(leaveDate.Text)+"' ";



            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            if (dtReader.HasRows)
            {
                CMD_INSERT.Enabled = false;
                
            }
            else {
                CMD_INSERT.Enabled = true;
            
            }
            //*** BindData to GridView ***//
            GridView2.DataSource = dtReader;
            GridView2.DataBind();
        }
        #endregion

        #region "BindDataleave_student()"
        void BindDataleave_student()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;
            //strSQL = "SELECT ecm.emp_id, " +
            //         "ecm.emp_cls_day, " +
            //         "ecm.emp_cls_start, " +
            //         "ecm.emp_cls_end, " +
            //         "ecm.emp_cls_total_hours, " +
            //         "ecm.emp_cls_hours, " +
            //         "ecm.emp_cls_mast_id, " +
            //         "em.emp_name, " +
            //         "em.emp_code, " +
            //         "crd.cls_reg_detail_id, " +
            //         "crd.cls_reg_id, " +
            //         "crd.cls_course_no, " +
            //         "crd.cls_course_time, " +
            //         "crd.cls_course_amt, " +
            //         "crm.cls_reg_id, " +
            //         "crm.cls_reg_code, " +
            //         "crm.level, " +
            //         "crm.cls_name, " +
            //         "concat(crm.cls_name,'(',crd.cls_course_time,')') as name_course, " +
            //         "cld.leave_date, cld.leave_hours, cld.leave_cls_start, cld.leave_cls_end, "+
            //         "cld.leave_type, cld.cls_leave_detail_id "+ 
            //    "FROM cls_leave_detail cld  "+
            //    "LEFT OUTER JOIN emp_cls_mast ecm " +
            //    "on cld.emp_cls_mast_id = ecm.emp_cls_mast_id "+
            //    "LEFT OUTER JOIN emp_mast em " +
            //    "ON ecm.emp_id = em.emp_id " +
            //    "LEFT OUTER JOIN cls_register_detail crd " +
            //    "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
            //    "LEFT OUTER JOIN cls_register_mast crm " +
            //    "ON crd.cls_reg_id = crm.cls_reg_id " ;
            // " where ecm.emp_id = " + empId.Text + " ";

            strSQL = "SELECT sm.st_id, " +
                     "sm.st_name, " +
                     "sm.st_code, " +
                     "cld.leave_date, " +
                     "cld.leave_hours, " +
                     "cld.leave_type, " +
                     "cld.cls_leave_detail_id, " +
                     "cld.cus_type, " +
                     "IF(cld.leave_type = '0', 'ลาล่วงหน้า', 'ลาทีหลัง') AS leave_type_desc, " +
                     "IF(cld.cus_type = '0', 'ครู', IF(cld.cus_type = '1', 'นักเรียน','พนักงาน')) AS cus_type_desc " +
                     "FROM    cls_leave_detail cld " +
                     "LEFT OUTER JOIN " +
                     "student_mast sm " +
                     "ON cld.st_id = sm.st_id " +
                     " where cld.st_id = '" + stId.Text + "' " +
                     " and cld.leave_date = '" + toDate(leaveDate.Text) + "' ";



            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            if (dtReader.HasRows)
            {
                CMD_INSERT_student.Enabled = false;

            }
            else
            {
                CMD_INSERT_student.Enabled = true;

            }
            //*** BindData to GridView ***//
            GridView4.DataSource = dtReader;
            GridView4.DataBind();
        }
        #endregion

        #region "BindDataleave_customer()"
        void BindDataleave_customer()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            

            strSQL = "SELECT cld.emp_id, " +
                     "em.emp_name, " +
                     "em.emp_code, " +
                     "cld.leave_date, " +
                     "cld.leave_hours, " +
                     "cld.leave_type, " +
                     "cld.cls_leave_detail_id, " +
                     "cld.cus_type, " +
                     "IF(cld.leave_type = '0', 'ลาล่วงหน้า', 'ลาทีหลัง') AS leave_type_desc, " +
                     "IF(cld.cus_type = '0', 'ครู', IF(cld.cus_type = '1', 'นักเรียน','พนักงาน')) AS cus_type_desc " +
                     "FROM    cls_leave_detail cld " +
                     "LEFT OUTER JOIN " +
                     "emp_mast em " +
                     "ON cld.emp_id = em.emp_id " +
                     " where cld.emp_id = '" + cusId.Text + "' " +
                     " and cld.leave_date = '" + toDate(leaveDate.Text) + "' ";



            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            if (dtReader.HasRows)
            {
                CMD_INSERT_customer.Enabled = false;

            }
            else
            {
                CMD_INSERT_customer.Enabled = true;

            }
            //*** BindData to GridView ***//
            GridView5.DataSource = dtReader;
            GridView5.DataBind();
        }
        #endregion

        #region "toDate"
        public static String toDate(String date)
        {
            String day = date.Split('/')[0];
            String month = date.Split('/')[1];
            String year = (Convert.ToInt32(date.Split('/')[2]) - 543).ToString();
            //String year = date.Split('/')[2];

            //DateTime dateForMat = DateTime.Parse((day + "/" + month + "/" + year));
            //String dateUs = string.Format("{0:yyyy/MM/dd HH:mm:ss}", dateForMat);
            //return dateUs;
            return string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.Parse((day + "/" + month + "/" + year)));
        }
        #endregion

        #region "button ค้นหาครูที่ต้องโทรไปบอก"
        protected void Button1_Click(object sender, EventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;



            strSQL = " SELECT es.schedule_emp_id, es.emp_dateschedule, es.emp_cls_mast_id, es.emp_id,ss.Schedule_id, ss.st_dateschedule, ss.st_id, " +
       " ecm.emp_cls_day, ecm.emp_cls_start, ecm.emp_cls_end, ecm.emp_cls_total_hours, ecm.emp_cls_hours, ecm.cls_reg_detail_id, " +
       " ecm.start_date,crd.cls_reg_id, crd.cls_course_no, crd.cls_course_time, crd.cls_course_amt, " +
       " crm.cls_reg_code, crm.cls_name ,em.emp_code, em.emp_name, em.emp_phone, sm.st_code, sm.st_name, sm.st_phone " +
       " FROM emp_schedule es " +
       " LEFT OUTER JOIN student_schedule ss " +
          " ON     es.emp_cls_mast_id = ss.emp_cls_mast_id " +
             " AND es.emp_dateschedule = ss.st_dateschedule " +
       " LEFT OUTER JOIN emp_cls_mast ecm " +
          " ON ecm.emp_cls_mast_id = es.emp_cls_mast_id " +
       " LEFT OUTER JOIN cls_register_detail crd " +
          " ON crd.cls_reg_detail_id = ecm.cls_reg_detail_id " +
       " LEFT OUTER JOIN cls_register_mast crm " +
          " ON crm.cls_reg_id = crd.cls_reg_id " +
       " LEFT OUTER JOIN emp_mast em " +
          " ON em.emp_id = es.emp_id " +
       " LEFT OUTER JOIN student_mast sm " +
          " ON sm.st_id = ss.st_id " +
             " WHERE     es.emp_id  = '" + Convert.ToInt32(empId.Text) + "' " +
             " AND es.emp_dateschedule = '" + toDate(leaveDate.Text) + "' ";


            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();

            //*** BindData to GridView ***//
            GridView1.DataSource = dtReader;
            GridView1.DataBind();
            BindDataleave_teacher();
        }
        #endregion

        #region "button ค้นหานักเรียนที่ต้องโทรไปบอก"
        protected void Button2_Click(object sender, EventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;

         try{   

            strSQL = " SELECT es.schedule_emp_id, es.emp_dateschedule, es.emp_cls_mast_id, es.emp_id,ss.Schedule_id, ss.st_dateschedule, ss.st_id, "+
       " ecm.emp_cls_day, ecm.emp_cls_start, ecm.emp_cls_end, ecm.emp_cls_total_hours, ecm.emp_cls_hours, ecm.cls_reg_detail_id, "+
       " ecm.start_date,crd.cls_reg_id, crd.cls_course_no, crd.cls_course_time, crd.cls_course_amt, "+
       " crm.cls_reg_code, crm.cls_name ,em.emp_code, em.emp_name, em.emp_phone, sm.st_code, sm.st_name, sm.st_phone "+
       " FROM emp_schedule es "+
       " LEFT OUTER JOIN student_schedule ss "+
          " ON     es.emp_cls_mast_id = ss.emp_cls_mast_id "+
             " AND es.emp_dateschedule = ss.st_dateschedule "+
       " LEFT OUTER JOIN emp_cls_mast ecm "+
          " ON ecm.emp_cls_mast_id = es.emp_cls_mast_id "+
       " LEFT OUTER JOIN cls_register_detail crd "+
          " ON crd.cls_reg_detail_id = ecm.cls_reg_detail_id "+
       " LEFT OUTER JOIN cls_register_mast crm "+
          " ON crm.cls_reg_id = crd.cls_reg_id "+
       " LEFT OUTER JOIN emp_mast em "+
          " ON em.emp_id = es.emp_id "+
       " LEFT OUTER JOIN student_mast sm "+
          " ON sm.st_id = ss.st_id "+
             " WHERE     ss.st_id  = '" + Convert.ToInt32(stId.Text) + "' " +
             " AND ss.st_dateschedule = '" + toDate(leaveDate.Text) + "' ";


            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            if (dtReader.HasRows)
            {
                //*** BindData to GridView ***//
                GridView3.DataSource = dtReader;
                GridView3.DataBind();
                BindDataleave_student();
            }
            else {
                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert112", "alert('ไม่พบข้อมูล')", true);
            }
            BindDataleave_student();
            }
            catch (Exception ex)
            {
                //ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert111", "alert(" + oError+ ")", true);
            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert111", "alert('กรุณากรอกข้อมูลให้ครบก่อนค้นหา')", true);
            }
        }
        #endregion

        #region "button ค้นหาพนักงาน"
        protected void Button3_Click(object sender, EventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;

          

                strSQL = "SELECT cld.emp_id, " +
                         "em.emp_name, " +
                         "em.emp_code, " +
                         "cld.leave_date, " +
                         "cld.leave_hours, " +
                         "cld.leave_type, " +
                         "cld.cls_leave_detail_id, " +
                         "cld.cus_type, " +
                         "IF(cld.leave_type = '0', 'ลาล่วงหน้า', 'ลาทีหลัง') AS leave_type_desc, " +
                         "IF(cld.cus_type = '0', 'ครู', IF(cld.cus_type = '1', 'นักเรียน','พนักงาน')) AS cus_type_desc " +
                         "FROM    cls_leave_detail cld " +
                         "LEFT OUTER JOIN " +
                         "emp_mast em " +
                         "ON cld.emp_id = em.emp_id " +
                         " where cld.emp_id = '" + cusId.Text + "' " +
                         " and cld.leave_date = '" + toDate(leaveDate.Text) + "' ";



                Conn.Open();
                MySqlDataReader dtReader;
                objCmd = new MySqlCommand(strSQL, Conn);
                dtReader = objCmd.ExecuteReader();

                //*** BindData to GridView ***//
                GridView5.DataSource = dtReader;
                GridView5.DataBind();
            
        }
        #endregion

        #region "GridView2_RowDeleting for teacher"
        protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM cls_leave_detail WHERE cls_leave_detail_id ='" + GridView2.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();

            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert115", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);
            

            
            GridView2.EditIndex = -1;

            BindDataleave_teacher();

        }
        #endregion

        #region "GridView4_RowDeleting for student"
        protected void GridView4_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM cls_leave_detail WHERE cls_leave_detail_id ='" + GridView4.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();

            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert116", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);



            GridView4.EditIndex = -1;

            BindDataleave_student();

        }
        #endregion

        #region "GridView5_RowDeleting for customer"
        protected void GridView5_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM cls_leave_detail WHERE cls_leave_detail_id ='" + GridView4.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();

            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert119", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);



            GridView4.EditIndex = -1;

            BindDataleave_customer();

        }
        #endregion

        

        

    }
}